﻿using Oracle.ManagedDataAccess.Client;
using SharpDB.ManagedOracle;
using SharpDB.Utils;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace SharpDB.ManagedOracle
{
    public partial class OracleDB
    {
        #region Bool值返回

        /// <summary>
        /// 尝试连接
        /// </summary>
        /// <returns></returns>
        public bool TryConnect()
        {
            DbConnection connn = null;
            try
            {
                connn = new OracleConnection(ConnectionString);
                connn.Open();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                if (connn != null)
                {
                    connn.Close();
                }
            }
        }

        public bool ValidateSql(string strSql, out string msg)
        {
            bool bResult = false;
            msg = string.Empty;
            using (DbConnection conn = new OracleConnection(ConnectionString))
            {
                DbCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                cmd.CommandTimeout = CmdTimeOut;
                conn.Open();
                try
                {
                    cmd.CommandText = "explain plan for " + strSql;
                    cmd.ExecuteNonQuery();
                    bResult = true;
                }
                catch (Exception ex)
                {
                    msg = ex.Message;
                    bResult = false;
                }
            }
            return bResult;
        }


        #endregion

        #region TReturn、DataRow、DataTable、DataSet、DbDataReader 返回
        public TReturn QrySingle<TReturn>(string strSql,params OracleParam[] cmdParms)
        {
            using (DbConnection conn = new OracleConnection(ConnectionString))
            {
                DbCommand cmd = new OracleCommand();
                try
                {
                    PrepareCommand(cmd, conn, null, strSql, cmdParms, CmdTimeOut);
                    object obj = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    return Util.ChangeType<TReturn>(obj);
                }
                catch (Exception ex)
                {
                    if (this.OnError != null)
                        this.OnError.Invoke(this, new OnErrorEventArgs(new string[] { cmd.CommandText }, cmd.Parameters, ex));
                    throw ex;
                }
            }
        }

        public DataRow QryRow(string strSql, params OracleParam[] cmdParms)
        {
            DataTable data = QryTable(strSql, cmdParms);
            if (data != null && data.Rows.Count > 0)
            {
                return data.Rows[0];
            }
            return null;
        }

        public DataTable QryTable(string strSql,  params OracleParam[] cmdParms)
        {
            using (OracleConnection conn = new OracleConnection(ConnectionString))
            {
                OracleCommand cmd = new OracleCommand(strSql, conn);
                PrepareCommand(cmd, conn, null, strSql, cmdParms, CmdTimeOut);
                using (DbDataAdapter da = new OracleDataAdapter(cmd))
                {
                    List<DataTable> lstTab = new List<DataTable>();
                    DataTable dt = new DataTable("dt");
                    try
                    {
                        da.Fill(dt);
                        cmd.Parameters.Clear();
                    }
                    catch (Exception ex)
                    {
                        if (this.OnError != null)
                            this.OnError.Invoke(this, new OnErrorEventArgs(new string[] { cmd.CommandText }, cmd.Parameters, ex));
                        throw ex;
                    }
                    return dt;
                }
            }
        }

        public List<DataTable> QryDS(string strSql, params OracleParam[] cmdParms)
        {
            using (OracleConnection conn = new OracleConnection(ConnectionString))
            {
                OracleCommand cmd = new OracleCommand(strSql, conn);
                PrepareCommand(cmd, conn, null, strSql, cmdParms, CmdTimeOut);                
                using (DbDataAdapter da = new OracleDataAdapter(cmd))
                {
                    List<DataTable> lstTab = new List<DataTable>();
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                        if (ds != null && ds.Tables.Count > 0)
                        {
                            foreach (DataTable table in ds.Tables)
                            {
                                lstTab.Add(table);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        if (this.OnError != null)
                            this.OnError.Invoke(this, new OnErrorEventArgs(new string[] { cmd.CommandText }, cmd.Parameters, ex));
                        throw ex;
                    }
                    return lstTab;
                }
            }
        }

        public DataTable QryTableByProc(string storedProcName, params OracleParam[] cmdParms)
        {
            DataTable dt = new DataTable("dt");
            using (OracleConnection conn = new OracleConnection(ConnectionString))
            {
                OracleCommand cmd = new OracleCommand(storedProcName, conn);
                try
                {
                    PrepareCommand(cmd, conn, null, storedProcName, cmdParms, CmdTimeOut, CommandType.StoredProcedure);
                    using (OracleDataAdapter adapter = new OracleDataAdapter(cmd))
                    {
                        adapter.Fill(dt);
                        cmd.Parameters.Clear();
                    }
                    return dt;
                }
                catch (Exception ex)
                {
                    if (this.OnError != null)
                        this.OnError.Invoke(this, new OnErrorEventArgs(new string[] { cmd.CommandText }, cmd.Parameters, ex));
                    throw ex;
                }
            }
        }

        public DataSet QryDSByProc(string storedProcName, params OracleParam[] cmdParms)
        {
            DataSet ds = new DataSet("ds");
            using (OracleConnection conn = new OracleConnection(ConnectionString))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand(storedProcName, conn);
                try
                {
                    PrepareCommand(cmd, conn, null, storedProcName, cmdParms, CmdTimeOut, CommandType.StoredProcedure);
                    DataAdapter adapter = new OracleDataAdapter(cmd);
                    adapter.Fill(ds);
                    cmd.Parameters.Clear();
                    return ds;
                }
                catch (Exception ex)
                {
                    if (this.OnError != null)
                        this.OnError.Invoke(this, new OnErrorEventArgs(new string[] { cmd.CommandText }, cmd.Parameters, ex));
                    throw ex;
                }
            }
        }

        public DbDataReader ExecReader(string strSql, params OracleParam[] cmdParms)
        {
            DbConnection conn = null;
            DbCommand cmd = new OracleCommand();
            try
            {
                conn = new OracleConnection(ConnectionString);
                PrepareCommand(cmd, conn, null, strSql, cmdParms, CmdTimeOut);
                DbDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return myReader;
            }
            catch (Exception ex)
            {
                if (this.OnError != null)
                    this.OnError.Invoke(this, new OnErrorEventArgs(new string[] { cmd.CommandText }, cmd.Parameters, ex));
                throw ex;
            }
        }
        #endregion

        #region 插入、更新、删除 执行命令/执行存储过程
        public int ExecSql(string strSql, params OracleParam[] cmdParms)
        {
            using (DbConnection conn = new OracleConnection(ConnectionString))
            {
                DbCommand cmd = new OracleCommand();
                try
                {
                    PrepareCommand(cmd, conn, null, strSql, cmdParms, CmdTimeOut);
                    int cnt = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return cnt;
                }
                catch (Exception ex)
                {
                    if (this.OnError != null)
                        this.OnError.Invoke(this, new OnErrorEventArgs(new string[] { cmd.CommandText }, cmd.Parameters, ex));
                    throw ex;
                }
            }
        }

        public int ExecSqlTran(params string[] sqlCmds)
        {
            if (sqlCmds == null || sqlCmds.Length == 0)
            {
                return -1;
            }
            using (DbConnection conn = new OracleConnection(ConnectionString))
            {
                conn.Open();
                DbCommand cmd = new OracleCommand();
                cmd.CommandTimeout = CmdTimeOut;
                cmd.Connection = conn;
                DbTransaction tran = conn.BeginTransaction();
                cmd.Transaction = tran;

                List<string> lstSql = null;
                //List<DbParameterCollection> lstParam = null;
                if (this.OnError != null)
                {
                    lstSql = new List<string>();
                    //lstParam = new List<DbParameterCollection>();
                }
                try
                {
                    int cnt = 0;
                    for (int n = 0; n < sqlCmds.Length; n++)
                    {
                        string strsql = sqlCmds[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            if (this.OnError != null)
                            {
                                lstSql.Add(cmd.CommandText);
                                //lstParam.Add(cmd.Parameters);
                            }
                            cnt += cmd.ExecuteNonQuery();
                        }
                    }
                    tran.Commit();
                    return cnt;
                }
                catch (Exception ex)
                {
                    if (this.OnError != null)
                        this.OnError.Invoke(this, new OnErrorEventArgs(lstSql.ToArray(), null, ex));
                    tran.Rollback();
                    return -1;
                }
            }
        }

        public int ExecSqlTran(Hashtable SqlCmdList)
        {
            using (DbConnection conn = new OracleConnection(ConnectionString))
            {
                conn.Open();
                using (DbTransaction trans = conn.BeginTransaction())
                {
                    DbCommand cmd = new OracleCommand();
                    List<string> lstSql = null;
                    List<DbParameterCollection> lstParam = null;
                    if (this.OnError != null)
                    {
                        lstSql = new List<string>();
                        lstParam = new List<DbParameterCollection>();
                    }
                    try
                    {
                        int count = 0;
                        //循环
                        foreach (DictionaryEntry myDE in SqlCmdList)
                        {
                            string cmdText = myDE.Key.ToString();
                            OracleParam[] cmdParms = ((List<OracleParam>)myDE.Value).ToArray();
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms, CmdTimeOut);
                            if (this.OnError != null)
                            {
                                lstSql.Add(cmd.CommandText);
                                lstParam.Add(cmd.Parameters);
                            }
                            count += cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                        return count;
                    }
                    catch (Exception ex)
                    {
                        if (this.OnError != null)
                            this.OnError.Invoke(this, new OnErrorEventArgs(lstSql.ToArray(), lstParam, ex));
                        trans.Rollback();
                        return -1;
                    }
                }
            }
        }
        
        public int ExecProc(string procName, params OracleParam[] cmdParms)
        {
            using (DbConnection conn = new OracleConnection(ConnectionString))
            {
                DbCommand cmd = new OracleCommand();
                try
                {
                    PrepareCommand(cmd, conn, null, procName, cmdParms, CmdTimeOut, CommandType.StoredProcedure);
                    int cnt = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return cnt;
                }
                catch (Exception ex)
                {
                    if (this.OnError != null)
                        this.OnError.Invoke(this, new OnErrorEventArgs(new string[] { cmd.CommandText }, cmd.Parameters, ex));
                    throw ex;
                }
            }
        }


        #endregion

        

    //    public bool BulkCopy(DataTable data, string tableName, Dictionary<string, string> columnMappings = null,
    //        int batchSize = 200000, int bulkCopyTimeout = 60)
    //    {
    //        List<string> lstAllColName = this.Info[tableName];
    //        OracleBulkCopy bulk = null;
    //        bulk = new OracleBulkCopy(this.ConnectionString);
    //        using (bulk)
    //        {
    //            if (columnMappings != null)
    //            {
    //                foreach (var colMapping in columnMappings)
    //                {
    //                    if (!lstAllColName.Contains(colMapping.Value, StringComparer.OrdinalIgnoreCase))
    //                    {
    //                        continue;
    //                    }
    //                    bulk.ColumnMappings.Add(new OracleBulkCopyColumnMapping(colMapping.Key, colMapping.Value));
    //                }
    //            }
    //            else
    //            {
    //                int colCount = data.Columns.Count;
    //                for (int j = 0; j < colCount; j++)
    //                {
    //                    if (!lstAllColName.Contains(data.Columns[j].ColumnName, StringComparer.OrdinalIgnoreCase))
    //                    {
    //                        data.Columns.Remove(data.Columns[j].ColumnName);
    //                    }
    //                    else
    //                    {
    //                        bulk.ColumnMappings.Add(new OracleBulkCopyColumnMapping(data.Columns[j].ColumnName, this.Info[tableName, data.Columns[j].ColumnName].ColumnName));
    //                    }
    //                }
    //            }
    //            bulk.DestinationTableName = tableName;
    //            bulk.BulkCopyTimeout = bulkCopyTimeout;
    //            bulk.BatchSize = batchSize;
    //            try
    //            {
    //                bulk.WriteToServer(data);
    //            }
    //            catch (Exception ex)
    //            {
    //                if (this.OnError != null)
    //                    this.OnError.Invoke(this, new OnErrorEventArgs(null, null, ex, tableName, bulk.ColumnMappings));
    //                throw ex;
    //            }
    //            finally
    //            {
    //                //清空，防止内存溢出
    //                lstAllColName = null;
    //                data = null;
    //            }               
    //            #region 自定义处理方式
    //            //if (data.Rows.Count <= batchSize)
    //            //{
    //            //    bulk.WriteToServer(data);
    //            //}
    //            //else
    //            //{
    //            //    int yushu = data.Rows.Count % batchSize;
    //            //    int copyCount = 0;
    //            //    if (yushu == 0)
    //            //    {
    //            //        copyCount = data.Rows.Count / batchSize;
    //            //    }
    //            //    else
    //            //    {
    //            //        copyCount = data.Rows.Count / batchSize + 1;
    //            //    }

    //            //    var dRows = data.AsEnumerable();

    //            //    for (int j = 0; j < copyCount; j++)
    //            //    {
    //            //        var drs = dRows.Skip(j * batchSize).Take(batchSize);
    //            //        bulk.WriteToServer(drs.ToArray());
    //            //    }
    //            //} 
    //            #endregion
    //        }
    //        return true;
    //    }


    //    public bool BulkCopy(DbDataReader reader, string tableName, Dictionary<string, string> columnMappings = null,
    //int batchSize = 200000, int bulkCopyTimeout = 60)
    //    {
    //        List<string> lstAllColName = this.Info[tableName];
    //        OracleBulkCopy bulk = null;
    //        bulk = new OracleBulkCopy(this.ConnectionString);
    //        using (bulk)
    //        {
    //            if (columnMappings != null)
    //            {
    //                foreach (var colMapping in columnMappings)
    //                {
    //                    if (!lstAllColName.Contains(colMapping.Value, StringComparer.OrdinalIgnoreCase))
    //                    {
    //                        continue;
    //                    }
    //                    bulk.ColumnMappings.Add(new OracleBulkCopyColumnMapping(colMapping.Key, colMapping.Value));
    //                }
    //            }
    //            else
    //            {
    //                int colCount = reader.FieldCount;
    //                for (int j = 0; j < colCount; j++)
    //                {
    //                    if (lstAllColName.Contains(reader.GetName(j), StringComparer.OrdinalIgnoreCase))
    //                    {
    //                        bulk.ColumnMappings.Add(new OracleBulkCopyColumnMapping(reader.GetName(j), reader.GetName(j)));
    //                    }
    //                }
    //            }
    //            bulk.DestinationTableName = tableName;
    //            bulk.BulkCopyTimeout = bulkCopyTimeout;
    //            bulk.BatchSize = batchSize;
    //            try
    //            {
    //                bulk.WriteToServer(reader);
    //            }
    //            catch (Exception ex)
    //            {
    //                if (this.OnError != null)
    //                    this.OnError.Invoke(this, new OnErrorEventArgs(null, null, ex, tableName, bulk.ColumnMappings));
    //                throw ex;
    //            }
    //            finally
    //            {
    //                lstAllColName = null;
    //                reader.Close();
    //            }               
    //            //清空，防止内存溢出                
    //            #region 自定义处理方式
    //            //if (data.Rows.Count <= batchSize)
    //            //{
    //            //    bulk.WriteToServer(data);
    //            //}
    //            //else
    //            //{
    //            //    int yushu = data.Rows.Count % batchSize;
    //            //    int copyCount = 0;
    //            //    if (yushu == 0)
    //            //    {
    //            //        copyCount = data.Rows.Count / batchSize;
    //            //    }
    //            //    else
    //            //    {
    //            //        copyCount = data.Rows.Count / batchSize + 1;
    //            //    }

    //            //    var dRows = data.AsEnumerable();

    //            //    for (int j = 0; j < copyCount; j++)
    //            //    {
    //            //        var drs = dRows.Skip(j * batchSize).Take(batchSize);
    //            //        bulk.WriteToServer(drs.ToArray());
    //            //    }
    //            //} 
    //            #endregion
    //        }
    //        return true;
    //    }


    }
}
